﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace 人员管理
{
    class PersonService
    {

        public List<Person> findAll()
        {
            List<Person> persons = new List<Person>();

            SqlConnection conn = SqlHelper.createConn();
            using (conn)
            {
                conn.Open();
                String sql = "select * from Person ";
                SqlCommand command = new SqlCommand(sql, conn);
                SqlDataReader sqlDataReader = command.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    object id = sqlDataReader["id"];
                    object name = sqlDataReader["name"];
                    object age = sqlDataReader["age"];
                    object sex = sqlDataReader["sex"];
                    object addr = sqlDataReader["addr"];

                    Person p = new Person();
                    p.ID = Convert.ToInt32(id);
                    p.Name = Convert.ToString(name);
                    p.Age = Convert.ToInt32(age);
                    p.Sex = Convert.ToString(sex);
                    p.Addr = Convert.ToString(addr);

                    persons.Add(p);
                }
            }
            return persons;
        }

        public int addPerson(Person p)
        {
            String sql = "insert into Person(name,age,sex,addr) values(@name,@age,@sex,@addr)";

            //SqlConnection conn = SqlHelper.createConn();
            //using (conn)
            //{
            //    conn.Open();
            //    SqlCommand command = new SqlCommand(sql, conn);
            //    command.Parameters.Add(new SqlParameter("@name", p.Name));
            //    command.Parameters.Add(new SqlParameter("@age", p.Age));
            //    command.Parameters.Add(new SqlParameter("@sex", p.Sex));
            //    command.Parameters.Add(new SqlParameter("@addr", p.Addr));
            //    int rows = command.ExecuteNonQuery();
            //    return rows;
            //}
            int count = SqlHelper.Execute(sql, new SqlParameter[] { 
                new SqlParameter("@name", p.Name), 
                new SqlParameter("@age", p.Age) ,
                new SqlParameter("@sex", p.Sex),
                new SqlParameter("@addr", p.Addr)
            });
            return count;
        }

        public int updatePerson(Person p)
        {
            String sql = "update Person set name = @name, age = @age, sex = @sex, addr = @addr where id = @id";
            int count = SqlHelper.Execute(sql, new SqlParameter[] { 
                new SqlParameter("@id", p.ID),
                new SqlParameter("@name", p.Name),
                new SqlParameter("@age", p.Age),
                new SqlParameter("@sex", p.Sex),
                new SqlParameter("@addr", p.Addr)
            });
            //SqlConnection conn = SqlHelper.createConn();
            //using (conn)
            //{
            //    conn.Open();
                
            //    SqlCommand command = new SqlCommand(sql, conn);
            //    command.Parameters.Add(new SqlParameter("@id", p.ID));
            //    command.Parameters.Add(new SqlParameter("@name", p.Name));
            //    command.Parameters.Add(new SqlParameter("@age", p.Age));
            //    command.Parameters.Add(new SqlParameter("@sex", p.Sex));
            //    command.Parameters.Add(new SqlParameter("@addr", p.Addr));
            //    int rows = command.ExecuteNonQuery();
            //    return rows;
            //}
            return count;
        }

        public int deletePerson(int id)
        {
            String sql = " delete Person where id = @ID ";
            int count = SqlHelper.Execute(sql, new SqlParameter[] { new SqlParameter("@ID", id) });
            //SqlConnection conn = SqlHelper.createConn();
            //using (conn)
            //{
            //    conn.Open();
            //    SqlCommand command = new SqlCommand(sql, conn);
            //    command.Parameters.Add(new SqlParameter("@ID", id));
            //    int rows = command.ExecuteNonQuery();
            //    return rows;
            //}
            return count;
        }

        public List<Person> findPersonsByName(string name)
        {
            List<Person> persons = new List<Person>();

            SqlConnection conn = SqlHelper.createConn();
            using (conn)
            {
                conn.Open();
                String sql = "select id,name,age,sex,addr from Person where name = @name order by asc";
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.Add(new SqlParameter("@name", name));
                SqlDataReader sqlDataReader = command.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    object id = sqlDataReader["id"];
                    object name1 = sqlDataReader["name"];
                    object age = sqlDataReader["age"];
                    object sex = sqlDataReader["sex"];
                    object addr = sqlDataReader["addr"];

                    Person p = new Person();
                    p.ID = Convert.ToInt32(id);
                    p.Name = Convert.ToString(name1);
                    p.Age = Convert.ToInt32(age);
                    p.Sex = Convert.ToString(sex);
                    p.Addr = Convert.ToString(addr);

                    persons.Add(p);
                }
            }

            return persons;
        }

    }
}
